﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using University.Workshop.Model;
using System.Data.SqlClient;
using System.Globalization;
using System.Collections.ObjectModel;

namespace University.Workshop.DataAccess
{
    public static class TicketDataAccess
    {
        public const string ConnString = "Data Source=.\\sqlexpress; Initial Catalog=LQSdataBase; Integrated Security=true";

        public static Ticket RegisterTicket(Ticket newTicket)
        {
            if (newTicket != null)
            {
                SqlConnection conn = null;
                try
                {
                    conn = new SqlConnection(ConnString);
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "INSERT INTO [TICKET](IssueTitle, Description, Date, OrderId, Priority, Status)" +
                                       " VALUES(@issueTitle, @description, @date, @orderId, @priority, @status)";
                    cmd.Parameters.AddWithValue("issueTitle", newTicket.IssueTitle);
                    cmd.Parameters.AddWithValue("description", newTicket.Description);
                    cmd.Parameters.AddWithValue("date", newTicket.Date);
                    cmd.Parameters.AddWithValue("orderId", newTicket.OrderId);
                    cmd.Parameters.AddWithValue("priority", newTicket.Priority);
                    cmd.Parameters.AddWithValue("status", newTicket.Status);
                    SqlCommand cmdId = conn.CreateCommand();
                    cmdId.CommandText = "SELECT @@Identity";
                    conn.Open();
                    int result = cmd.ExecuteNonQuery();
                    if (result <= 0)
                    {
                        throw new DataAccessException("An error has ocurred while sending the ticket");
                    }
                    newTicket.Id = Convert.ToInt64(cmdId.ExecuteScalar(), CultureInfo.CurrentCulture);
                    return newTicket;
                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("an unknown error ocurred", ex);
                }
                finally
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }
            return null;
        }


        public static Ticket GetTicketById(long ticketId)
        {
            Ticket ticket = new Ticket();
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT * FROM [TICKET] WHERE Id=@ticketId";
                command.Parameters.AddWithValue("ticketId", ticketId);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                while (reader.Read())
                {
                    
                    ticket.Id = Int64.Parse(reader["Id"].ToString(), CultureInfo.CurrentCulture);
                    ticket.IssueTitle = reader["IssueTitle"].ToString();
                    ticket.Description = reader["Description"].ToString();
                    ticket.Date = (DateTime)reader["Date"];
                    ticket.OrderId = Int64.Parse(reader["OrderId"].ToString(), CultureInfo.CurrentCulture);
                    ticket.Priority = Int16.Parse(reader["Priority"].ToString(), CultureInfo.CurrentCulture);
                    ticket.Status = TicketStatus.New + Int16.Parse(reader["Status"].ToString(), CultureInfo.CurrentCulture);
                }
                return ticket;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting ticket", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }


        public static Ticket GetTicketByOrderId(long orderId)
        {
            Ticket ticket = new Ticket();
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT * FROM [TICKET] WHERE OrderId=@orderId";
                command.Parameters.AddWithValue("orderId", orderId);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                while (reader.Read())
                {
                    ticket.Id = Int64.Parse(reader["Id"].ToString(), CultureInfo.CurrentCulture);
                    ticket.IssueTitle = reader["IssueTitle"].ToString();
                    ticket.Description = reader["Description"].ToString();
                    ticket.Date = (DateTime)reader["Date"];
                    ticket.OrderId = Int64.Parse(reader["OrderId"].ToString(), CultureInfo.CurrentCulture);
                    ticket.Priority = Int16.Parse(reader["Priority"].ToString(), CultureInfo.CurrentCulture);
                    ticket.Status = TicketStatus.New + Int16.Parse(reader["Status"].ToString(), CultureInfo.CurrentCulture);
                }
                return ticket;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting ticket", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static Product GetProductByTicketId(long ticketId)
        {
            Product product = new Product();
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT * FROM [PRODUCT] WHERE ID in (SELECT ProductId FROM [ORDER] where [ORDER].[Id] in (SELECT OrderId FROM [TICKET] WHERE [TICKET].[Id]=@ticketId))";
                command.Parameters.AddWithValue("ticketId", ticketId);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                while (reader.Read())
                {
                    product.ProductId = Int64.Parse(reader["ID"].ToString(), CultureInfo.CurrentCulture);
                    product.SellerId = Int64.Parse(reader["SellerID"].ToString(), CultureInfo.CurrentCulture);
                    product.Name = reader["Name"].ToString();
                    product.Description = reader["Description"].ToString();
                    product.Category = reader["Category"].ToString();
                    product.Postdate = DateTime.Parse(reader["PosDate"].ToString(), CultureInfo.CurrentCulture);
                    product.Price = float.Parse(reader["Price"].ToString(), CultureInfo.CurrentCulture);
                    product.Currency = reader["Currency"].ToString();
                    int images = int.Parse(reader["Images"].ToString(), CultureInfo.CurrentCulture);
                    product.HasImages = images != 0;
                    product.Status = reader["Status"].ToString();
                    product.Quantity = short.Parse(reader["Quantity"].ToString(), CultureInfo.CurrentCulture);
                }
                return product;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting product", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static Collection<Ticket> GetAllTicketsByBuyerId(long buyerId)
        {
            Collection<Ticket> allTickets = new Collection<Ticket>();
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT * FROM [TICKET] WHERE OrderId In (Select Id FROM [Order] WHERE BuyerId=@buyerId AND Status=@status)";
                command.Parameters.AddWithValue("buyerId", buyerId);
                command.Parameters.AddWithValue("status", "Completed");
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                while (reader.Read())
                {
                    Ticket ticket = new Ticket();
                    ticket.Id = Int64.Parse(reader["Id"].ToString(), CultureInfo.CurrentCulture);
                    ticket.IssueTitle = reader["IssueTitle"].ToString();
                    ticket.Description = reader["Description"].ToString();
                    ticket.Date = (DateTime)reader["Date"];
                    ticket.OrderId = Int64.Parse(reader["OrderId"].ToString(), CultureInfo.CurrentCulture);
                    ticket.Priority = Int16.Parse(reader["Priority"].ToString(), CultureInfo.CurrentCulture);
                    ticket.Status = TicketStatus.New + Int16.Parse(reader["Status"].ToString(), CultureInfo.CurrentCulture);
                    allTickets.Add(ticket);
                }
                return allTickets;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting ticket", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static Collection<Ticket> GatherActiveTickets()
        {
            Collection<Ticket> tickets = new Collection<Ticket>();
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT * FROM [TICKET] WHERE Status=@status1 OR Status=@status2";
                command.Parameters.AddWithValue("status1", 0);
                command.Parameters.AddWithValue("status2", 1);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                while (reader.Read())
                {
                    Ticket ticket = new Ticket();
                    ticket.Id = Int64.Parse(reader["Id"].ToString(), CultureInfo.CurrentCulture);
                    ticket.IssueTitle = reader["IssueTitle"].ToString();
                    ticket.Description = reader["Description"].ToString();
                    ticket.Date = (DateTime)reader["Date"];
                    ticket.OrderId = Int64.Parse(reader["OrderId"].ToString(), CultureInfo.CurrentCulture);
                    ticket.Priority = Int16.Parse(reader["Priority"].ToString(), CultureInfo.CurrentCulture);
                    ticket.Status = TicketStatus.New + Int16.Parse(reader["Status"].ToString(), CultureInfo.CurrentCulture);
                    tickets.Add(ticket);
                }
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting tickets", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
            return tickets;
        }

        public static TicketReply RegisterTicketReply(TicketReply ticketReply)
        {
            if (ticketReply != null)
            {
                SqlConnection conn = null;
                try
                {
                    conn = new SqlConnection(ConnString);
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "INSERT INTO [TICKETREPLY](TicketId, UserId, Reply, Date)" +
                                       " VALUES(@ticketId, @userId, @reply, @date)";
                    cmd.Parameters.AddWithValue("ticketId", ticketReply.TicketId);
                    cmd.Parameters.AddWithValue("userId", ticketReply.UserId);
                    cmd.Parameters.AddWithValue("reply", ticketReply.Reply);
                    cmd.Parameters.AddWithValue("date", ticketReply.Date);
                    SqlCommand cmdId = conn.CreateCommand();
                    cmdId.CommandText = "SELECT @@Identity";
                    conn.Open();
                    int result = cmd.ExecuteNonQuery();
                    if (result <= 0)
                    {
                        throw new DataAccessException("An error has ocurred while sending the ticket");
                    }
                    ticketReply.Id = Convert.ToInt64(cmdId.ExecuteScalar(), CultureInfo.CurrentCulture);
                    return ticketReply;
                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("an unknown error ocurred", ex);
                }
                finally
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }
            return null;
        }

        public static Collection<TicketReply> GetAllTicketReplies(long ticketId)
        {
            Collection<TicketReply> allTicketReplies = new Collection<TicketReply>();
            TicketReply ticketReply;
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT * FROM [TICKETREPLY] WHERE TicketId=@ticketId";
                command.Parameters.AddWithValue("ticketId", ticketId);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                while (reader.Read())
                {
                    ticketReply = new TicketReply();
                    ticketReply.Id = Int64.Parse(reader["Id"].ToString(), CultureInfo.CurrentCulture);
                    ticketReply.TicketId = Int64.Parse(reader["TicketId"].ToString(), CultureInfo.CurrentCulture);
                    ticketReply.UserId = Int64.Parse(reader["UserId"].ToString(), CultureInfo.CurrentCulture);
                    ticketReply.Reply = reader["Reply"].ToString();
                    ticketReply.Date = (DateTime)reader["Date"];
                    allTicketReplies.Add(ticketReply);
                }
                return allTicketReplies;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting ticket", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static Ticket ChangeTicketStatus(long ticketId, TicketStatus status)
        {
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnString);
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "UPDATE [TICKET] SET Status=@status WHERE Id=@ticketId";
                cmd.Parameters.AddWithValue("ticketId", ticketId);
                cmd.Parameters.AddWithValue("status", (int)status);
                connection.Open();
                int resp = cmd.ExecuteNonQuery();
                if (resp <= 0)
                {
                    return null;
                }
                return GetTicketById(ticketId);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error updating ticket information", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
    }
}
